﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CommonLibrary.DB;

namespace ServiceApplication.Dao.Basic
{
    class SrchDao
    {
        public DataTable QuerySendMsgForSearch(String userId, String searchText)
        {
            String sql = "SELECT [msg_id], [msg_titile], [msg_sendtime], [msg_content] FROM jc_msg " +
                "WHERE ([msg_titile] like '%' + @srchText + '%' OR [msg_content] like '%' + @srchText + '%') AND [creater_id] = @userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryReceiveMsgForSearch(String userId, String searchText)
        {
            String sql = "SELECT [msg_id], [msg_titile], [msg_sendtime], [msg_content], [receiver_ids] FROM jc_msg " +
                "WHERE [msg_titile] like '%' + @srchText + '%' OR [msg_content] like '%' + @srchText + '%'";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string receiver_ids = dt.Rows[i]["receiver_ids"].ToString();
                string[] receivers = receiver_ids.Split(new char[] { ',' });

                if (!receivers.Contains(userId.ToString()))
                {
                    dt.Rows.RemoveAt(i);
                    i--;
                    continue;
                }
            }

            dt.AcceptChanges();

            return dt;
        }

        public DataTable QueryFormListForSearch(String userId, String searchText)
        {
            String sql = "SELECT [form_id], [name], [createtime], [descn] FROM wf_form " +
                "WHERE ([name] like '%' + @srchText + '%' OR [descn] like '%' + @srchText + '%') AND  [user_id]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());

            return dt;
        }

        public DataTable QueryNotifyForSearch(String userId, String searchText)
        {
            String sql = "SELECT [notify_id], [notify_title], [notify_createtime], [notify_content] FROM plan_notify " +
                "WHERE ([notify_title] like '%' + @srchText + '%' OR [notify_content] like '%' + @srchText + '%') AND [creater_id]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryWfTemplateForSearch(String userId, String searchText)
        {
            String sql = "SELECT [wftemplate_id], [wftemplate_name], [creater_time], [wftemplate_name] FROM wf_template " +
                "WHERE ([wftemplate_name] like '%' + @srchText + '%' OR [wftemplate_name] like '%' + @srchText + '%') AND [creater_id]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryWfInsForSearch(String userId, String searchText)
        {
            String sql = "SELECT [uniqueid], [insname], [createdate], [ins_descn] FROM wf_ins " +
                "WHERE ([insname] like '%' + @srchText + '%' OR [ins_descn] like '%' + @srchText + '%') AND [user_id]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryWfTaskForSearch(String userId, String searchText)
        {
            String sql = "SELECT [task_id], [taskname], [starttime], [taskname] FROM wf_task " +
                "WHERE ([taskname] like '%' + @srchText + '%' OR [taskname] like '%' + @srchText + '%') AND [fqrcode]=@userId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@srchText", searchText));
            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }
    }
}
